import pandas as pd

def main(inData):
    #open the FCC data input
    df = pd.read_csv(inData)
    
    #remove techcodes 0 and 60 (only interested in wired broadband)
    dropTechs = [0,60]
    df = df[-df['TechCode'].isin(dropTechs)]

    #remove providers that don't provide service to residential or business
    keepCols = ['Consumer', 'Business']
    df[(df[keepCols]!=0).any(axis=1)]
    
    #remove US territories
    dropStates = ['AS','GU','MP','PR','VI']
    df = df[-df['StateAbbr'].isin(dropStates)]
    
    #add 2010 tract ID
    df['Tract'] = 0
    for index, row in df.iterrows():
        if len(str(row['BlockCode']))==15:
            df.iloc[index, df.columns.get_loc('Tract')] = str(row['BlockCode'])[0:11]
        elif len(row['BlockCode'])==14:
            df.iloc[index, df.columns.get_loc('Tract')] = str(row['BlockCode'])[0:10]
    
    #get a list of the unique tracts
    tracts = (df['Tract']).unique()
    
    #create a final results df
    #adjust accordingly for year(s) of interest, etc.
    finaldf = pd.DataFrame()

    finaldf['Tract'] = tracts.tolist()
    finaldf['prov14'] = 0
    finaldf['prov15'] = 0
    finaldf['prov16'] = 0
    finaldf['prov17'] = 0
    
    for index, row in finaldf.iterrows():
        tt = row['Tract']
        
        #isolate relevant FCC entries
        tdf = df[df['Tract']==tt]
        
        #change prov column to year of interest
        #count unique providers
        finaldf.iloc[index, finaldf.columns.get_loc('prov17')] = len(set(tdf['HocoNum']))
        
    #write results, change name based on year of interest
    finaldf.to_csv(r'D:\Work\BIDS\Final Scripts\2008-2010\results17.csv')

if __name__ == '__main__':
    #change this path to the relevant FCC in data
    #datasets can be found at:
    #https://www.fcc.gov/general/broadband-deployment-data-fcc-form-477
    main(inData=r'D:\Work\BIDS\Final Scripts\2014-2018\fbd_us_without_satellite_dec2017_v3.csv')